#!/bin/bash
#声明当前脚本使用bash解释器质性
#接收外部传入的日期参数，如果外部没有传入日期参数，怎么办？
#严格:外部没有传入，就退出 exit  非严格：如果外部没有传入，自己生成一个默认的日期(当前机器时间的昨天)
#-n代表判断字符串的长度，如果字符串长度为0，返回false,否则返回true
if [ -n "$1" ]
then
	do_date=$1
else
	#echo 必须给我传一个日期
	#exit
	do_date= $(date -d yesterday +%F) 
fi

echo 当前要操作的日期是$do_date
dws_play_course_video_nd="
   insert overwrite table dws_play_course_video_nd partition (dt=$do_date)
select
    sum(`if`(dt > date_sub($do_date,7) , user_count, 0)) user_count_7d   ,
    sum(user_count) user_count_30d  ,
   sum(`if`(dt > date_sub($do_date,7) , sum_play_sec, 0)) sum_play_sec_7d ,
   sum(sum_play_sec) sum_play_sec_30d,
   sum(`if`(dt > date_sub($do_date,7) , sid_count, 0)) sid_count_7d    ,
   sum(sid_count) sid_count_30d   ,
   video_id        ,
   video_name      ,
   play_time       ,
   course_id       ,
   course_name     ,
   chapter_id      ,
   chapter_name
from dws_play_course_video_1d
where dt > date_sub($do_date,30)
group by video_id, video_name, play_time, course_id, course_name, chapter_id, chapter_name; 
"
dws_play_course_course_nd="
    insert overwrite table dws_play_course_course_nd partition (dt=$do_date)
select
       id             ,
        course_id        ,
        course_name      ,
        chapter_num      ,
        sum(`if`(dt > date_sub($do_date,7) , count_chapter, 0)) count_chapter_7d ,
        sum(`if`(dt > date_sub($do_date,7) , count_user, 0)) count_user_7d    ,
        sum(`if`(dt > date_sub($do_date,7) , counted_user, 0)) counted_user_7d  ,
        sum(count_chapter) count_chapter_30d,
        sum(count_user) count_user_30d   ,
        sum(counted_user) counted_user_30d ,
        create_time      ,
        position_sec     ,
        during_sec       ,
        play_sec         ,
        sid              ,
        video_id         ,
        video_name       ,
        video_status     ,
        version_id
from dws_play_course_course_1d
where dt > date_sub($do_date,30)
group by course_id, id, course_name, chapter_num, create_time, position_sec, during_sec, play_sec, sid, video_id, video_name, video_status, version_id;

"
dws_trade_user_cart_add_nd"
insert overwrite table dws_trade_user_cart_add_nd partition (dt=$do_date)
select
       user_id,
       sum(`if`(dt > date_sub($do_date,7),cart_add_count_1d,0)) cart_add_count_7d,
       sum(`if`(dt > date_sub($do_date,7),cart_add_num_1d,0)) cart_add_num_7d,
       sum(cart_add_count_1d) cart_add_count_30d,
       sum(cart_add_num_1d) cart_add_num_30d
from dws_trade_user_cart_add_1d
where dt > date_sub($do_date,30)
group by user_id;
"
dws_trade_user_payment_nd"
insert overwrite table dws_trade_user_payment_nd partition (dt=$do_date)
select
        user_id,
        sum(`if`(dt > date_sub($do_date,7),payment_count_1d,0)) payment_count_7d,
        sum(`if`(dt > date_sub($do_date,7),payment_num_1d,0)) payment_num_7d,
        sum(`if`(dt > date_sub($do_date,7),payment_amount_1d,0)) payment_amount_7d,
        sum(payment_count_1d) payment_count_30d,
        sum(payment_num_1d) payment_num_30d,
        sum(payment_amount_1d) payment_amount_30d
from dws_trade_user_payment_1d
where dt > date_sub($do_date,30)
group by user_id;
"
dws_traffic_page_visitor_page_view_nd"
insert overwrite table dws_traffic_page_visitor_page_view_nd partition (dt=$do_date)
select
        mid_id,
        brand,
        model,
        operate_system,
        page_id,
        sum(`if`(dt > date_sub($do_date,7),during_time_1d,0)) during_time_7d,
        sum(`if`(dt > date_sub($do_date,7),view_count_1d,0)) view_count_7d,
        sum(during_time_1d) during_time_30d,
        sum(view_count_1d) view_count_30d
from dws_traffic_page_visitor_page_view_1d
where dt > date_sub($do_date,30)
group by page_id,mid_id,brand,
        model,
        operate_system;
"
dws_user_user_login_td"
insert overwrite table dws_user_user_login_td partition (dt=$do_date)
select
        nvl(dws_1d.user_id , dws_td.user_id) user_id,
        nvl(dws_1d.dt,login_date_last) login_date_last,
        nvl(login_count_td,0) + nvl(login_count,0) login_count_td
from
    (
    select
    *
    from dws_user_user_login_td
    where dt=$do_date
    ) dws_td
full join
    (
    select
        user_id,
        $do_date dt,
        count(*) login_count
    from dwd_user_login_inc
    where dt=$do_date
    group by user_id
    ) dws_1d
on dws_td.user_id = dws_1d.user_id;
"
dws_class_course_order_nd="
insert overwrite table dws_class_course_order_nd partition (dt='$do_date')
select course_id,
       course_name,
       subject_id,
       subject_name,
       category_id,
       category_name,
       sum(`if`(dt>date_sub('$do_date',7),order_count_1d,0))order_count_7d,
       sum(`if`(dt>date_sub('$do_date',7),number_orders_1d,0))number_orders_7d,
       sum(`if`(dt>date_sub('$do_date',7),order_amount_1d,0))order_amount_7d,
       sum(order_count_1d)order_count_30d,
       sum(number_orders_1d)number_orders_30d,
       sum(order_amount_1d)order_amount_30d
from dws_class_course_order_1d
where dt>date_sub('$do_date',30)
group by course_id,
       course_name,
       subject_id,
       subject_name,
       category_id,
       category_name;
"
dws_class_course_review_nd="
insert overwrite table dws_class_course_review_nd partition (dt)
select course_id,
       course_name,
       avg(`if`(dt>date_sub('$do_date',7),review_avg_1d,0)) review_avg_7d,
       avg(`if`(dt>date_sub('$do_date',7),review_user_count_1d,0)) review_user_count_7d,
       avg(`if`(dt>date_sub('$do_date',7),rate_1d,0)) rate_7d,
       avg(review_avg_1d) review_avg_30d,
       avg(review_user_count_1d) review_user_count_30d,
       avg(rate_1d) rate_30d,
       dt
from dws_class_course_review_1d
where dt>date_sub('$do_date',30)
group by course_id, course_name;
"
dws_class_course_audition_nd="
insert overwrite table dws_class_course_audition_nd partition (dt)
select course_id,
       course_name,
       subject_id,
       subject_name,
       category_id,
       category_name,
       avg(`if`(dt>date_sub('$do_date',7),audition_count_1d,0)) audition_count_7d,
       avg(`if`(dt>date_sub('$do_date',7),audition_retention_rates_1d,0)) audition_retention_rates_7d,
       avg(audition_count_1d)audition_count_30d,
       avg(audition_retention_rates_1d)audition_retention_rates_30d,
       dt
from dws_class_course_audition_1d
where dt>date_sub('$do_date',30)
group by course_id, course_name, subject_id, subject_name, category_id, category_name,dt;
"
dws_trade_user_order_1d partition = "
insert overwrite table dws_trade_user_order_1d partition (dt ='2022-07-05 )
select
       user_id,
       count(*) order_count_1d,
       sum(distinct user_id) user_amount_1d,
       sum(final_amount) order_total_amount_1d
from dwd_trade_order_detail_inc
where dt = '2022-07-05'
and isnotnull(user_id)
group by user_id,dt;"

dws_trade_province_order_1d = "
insert overwrite table dws_trade_province_order_1d partition (dt= '2022-07-05')
select
       province_name,
    --   user_id,
       count(*) order_count_1d,
       count(distinct user_id) user_amount_1d,
       sum(final_amount) order_total_amount_1d
from dwd_trade_order_detail_inc
where dt = '2022-07-05'
and isnotnull(user_id)
group by province_name;
"

dws_examine_course_exam_1d = "
insert overwrite table dws_examine_course_exam_1d partition (dt ='2022-07-05')

select
            count(*) user_count_1d,
            cast(sum(score)/count(*) as BIGINT)  score_avg_1d,
            cast(sum(duration_sec)/count(*) as BIGINT)  duration_sec_avg_1d
from dwd_examine_test_exam_inc
where dt = '2022-07-05';
"

case $2 in
"dws_play_course_video_nd")
	hive --database gmall -e "$dws_play_course_video_nd"
	;;
"dws_play_course_course_nd")
	hive --database gmall -e "$dws_play_course_course_nd"
	;;
"dws_trade_user_cart_add_nd")
hive --database gmall -e "$dws_trade_user_cart_add_nd"
;;
"dws_trade_user_payment_nd")
hive --database gmall -e "$dws_trade_user_payment_nd"
;;
"dws_traffic_page_visitor_page_view_nd")
hive --database gmall -e "$dws_traffic_page_visitor_page_view_nd"
;;
"dws_user_user_login_td")
hive --database gmall -e "$dws_user_user_login_td"
;;
"dws_class_course_order_nd")
hive --database gmall -e "$dws_class_course_order_nd"
;;
"dws_class_course_review_nd")
hive --database gmall -e "$dws_class_course_review_nd"
;;
"dws_class_course_audition_nd")
hive --database gmall -e "$dws_class_course_audition_nd"
;;
"dws_trade_user_order_1d")
hive --database gmall -e "$dws_trade_user_order_1d"
;;
"dws_trade_province_order_1d")
hive --database gmall -e "$dws_trade_province_order_1d"
;;
"dws_examine_course_exam_1d")
hive --database gmall -e "dws_examine_course_exam_1d"
;;
"all")
	hive --database gmall -e "$dws_play_course_video_nd$dws_play_course_course_nd$dws_trade_user_cart_add_nd$dws_trade_user_payment_nd$dws_traffic_page_visitor_page_view_nd$dws_user_user_login_td$dws_class_course_order_nd$dws_class_course_review_nd$dws_class_course_audition_nd$dws_trade_user_order_1d$dws_trade_province_order_1ddws_examine_course_exam_1d"
	;;
esac